﻿using Business.DB.Interface;
using Bussiness.DB.Model;
using System.Data.SqlClient;

namespace Business.DB.SqlServer
{
    public class SqlServerHelper : IDBHelper
    {
        string ConnectionString = "Data Source=WIN-20231020MLM;Initial Catalog=CustomerDB;Integrated Security=True;TrustServerCertificate=True;";
        public void Query()
        {
            //Console.WriteLine($"{this.GetType().Name}.Query");
        }

        public Company GetCompany(int id)
        {
            //1.准备数据库链接字符串
            //ConnectionString 

            //2.创建链接对象
            SqlConnection connection = new SqlConnection(ConnectionString);
            connection.Open();//打开链接 

            //3.准备命名，Sql语句
            string sql = "SELECT TOP (1000) [Id]\r\n      ,[Name]\r\n      ,[CreateTime]\r\n      ,[CreatorId]\r\n      ,[LastModifierId]\r\n      ,[LastModifyTime]\r\n  FROM [CustomerDB].[dbo].[Company] where id=13479";

            //4.准备命名执行对象 
            SqlCommand command = new SqlCommand(sql, connection);
            
            //5.执行命令（执行Sql语句）
            SqlDataReader reader = command.ExecuteReader();  //数据读取器

            //6.如何赋值给对象呢？
            //Company company = new Company();

            Type type = typeof(Company);
            object? oInstance = Activator.CreateInstance(type);

            if (reader.Read()) //如果读取到数据可，就可以输出数据
            {
                Console.WriteLine(reader["Name"]);
                Console.WriteLine(reader["Id"]);
                Console.WriteLine(reader["CreateTime"]);
                Console.WriteLine(reader["CreatorId"]);

                //反射设置值；
                foreach (var prop in type.GetProperties())
                {
                    #region MyRegion 
                    //if (prop.Name.Equals("Id"))
                    //{
                    //    //prop.SetValue(oInstance, reader["Id"]);
                    //    prop.SetValue(oInstance, reader[prop.Name]);
                    //}
                    //else if (prop.Name.Equals("Name"))
                    //{
                    //    //prop.SetValue(oInstance, reader["Name"]);
                    //    prop.SetValue(oInstance, reader[prop.Name]);
                    //}
                    //else if (prop.Name.Equals("CreateTime"))
                    //{
                    //    //prop.SetValue(oInstance, reader["CreateTime"]); 
                    //    prop.SetValue(oInstance, reader[prop.Name]);
                    //}
                    //else if (prop.Name.Equals("CreatorId"))
                    //{
                    //    //prop.SetValue(oInstance, reader["CreatorId"]);
                    //    prop.SetValue(oInstance, reader[prop.Name]);
                    //}
                    //else if (prop.Name.Equals("LastModifierId"))
                    //{
                    //    // prop.SetValue(oInstance, reader["LastModifierId"]);
                    //    prop.SetValue(oInstance, reader[prop.Name]);
                    //}
                    //else if (prop.Name.Equals("LastModifyTime"))
                    //{
                    //   // prop.SetValue(oInstance, reader["LastModifyTime"]);
                    //    prop.SetValue(oInstance, reader[prop.Name]);
                    //}
                    #endregion

                    prop.SetValue(oInstance, reader[prop.Name]);
                }
            }
            return (Company)oInstance;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public T Get<T>(int id) where T : BaseModel  //实体类必然会有一个Id属性；
        {            
            SqlConnection connection = new SqlConnection(ConnectionString);
            connection.Open();//打开链接 

            #region MyRegion
            //Sql不对~~ 不能写死，应该根据不同的类型生成不同的Sql语句；
            //string sql = "SELECT TOP (1000) [Id]\r\n      ,[Name]\r\n      ,[CreateTime]\r\n      ,[CreatorId]\r\n      ,[LastModifierId]\r\n      ,[LastModifyTime]\r\n  FROM [CustomerDB].[dbo].[Company] where id=13479";  
            //string sql = "SELECT {逗号分割的表字段名称} FROM {表名称} WHERE ID="+id; 
            //string sql = "SELECT {逗号分割的类的属性名称} FROM {实体名称} WHERE ID=" + id;
            #endregion 

            //Sql语句--反射---执行的同一个操作，sql语句重复生成多次；
            //同一个类型，Sql语句，有必要去没次都生成一次吗？---可以缓存下？？？ 提高性能；

            //Sql语句生成：
            //1. 同一个类型的Sql语句是一样的
            //2. 同一个类型的操作，Sql语句只需要生成一次；
            //3. 如果要缓存，为每一个类型做一个缓存？？？  为每一个类型生成缓存-----啥缓存？  泛型缓存；
            //   泛型缓存的本质是啥玩意儿？  泛型类~
            //string sql = $"SELECT {string.Join(',', type.GetProperties().Select(c => $"[{c.Name}]"))} FROM [{type.Name}] WHERE ID=" + id;  
            //string sql = SqlBuilder<T>.GetQuerySql(id); 


            //string sql = "SELECT TOP (1000) [Id]\r\n      ,[Name]\r\n      ,[CreateTime]\r\n      ,[CreatorId]\r\n      ,[LastModifierId]\r\n      ,[LastModifyTime]\r\n  FROM [CustomerDB].[dbo].[Company] where id=13479";

            
            Type type = typeof(T);
            object? oInstance = Activator.CreateInstance(type);

            SqlCommand command = new SqlCommand(SqlBuilder<T>.GetQuerySql(id), connection);
            SqlDataReader reader = command.ExecuteReader();  //数据读取器  
            if (reader.Read()) //如果读取到数据可，就可以输出数据
            {
                //反射设置值；
                foreach (var prop in type.GetProperties())
                {
                    prop.SetValue(oInstance, reader[prop.Name] is DBNull ? null : reader[prop.Name]);
                }
            }
            return (T)oInstance;
        }
    }
}
